package com.sqz.mybatisplus.demo.controller;

import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.sqz.mybatisplus.demo.model.User;
import com.sqz.mybatisplus.demo.service.IMybatisPlusTestService;
import com.sqz.mybatisplus.demo.util.InstanceUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.*;
import static com.sqz.mybatisplus.demo.util.HardWareUtils.getMac;


/**
 * @author sqz
 * @Description: Wrapper 类 控制层
 * @date 2020/06/25
 */
@RestController
@RequestMapping(WrapperController.PATH)
@Slf4j
public class WrapperController {

    /***
     * 控制层信息
     */
    private static final String CONTENT = "Wrapper层接口:";

    /***
     * 访问路径
     */
    public final static String PATH = "/wrapper";


    @Autowired
    private IMybatisPlusTestService mybatisPlusTestService;


    /**
     * 条件构造器的  AbstractWrapper 方法
     */
    @GetMapping("/abstract-wrapper")
    public String abstractWrapper() {

        //清除多余数据，不用关心
        UpdateWrapper<User> tempWrapper = new UpdateWrapper<>();
        tempWrapper.lambda().eq(User::getPassword,getMac());
        mybatisPlusTestService.remove(tempWrapper);

        // 先插入（批量）
        Collection<User> entityList = new ArrayList<>();
        User user1 = InstanceUtil.getUser(CONTENT + "wrapper1");
        User user2 = InstanceUtil.getUser(CONTENT + "wrapper2");
        User user3 = InstanceUtil.getUser(CONTENT + "wrapper3");
        user3.setMobile(null);
        User user4 = InstanceUtil.getUser(CONTENT + "wrapper4");
        User user5 = InstanceUtil.getUser(CONTENT + "wrapper5");
        entityList.add(user1);
        entityList.add(user2);
        entityList.add(user3);
        entityList.add(user4);
        entityList.add(user5);
        mybatisPlusTestService.saveBatch(entityList);
        entityList.forEach(s -> log.info("\r\n批量新增：\r\n" + s));

        //查询条件构造器
        QueryWrapper<User> wrapper ;

        //创建 key 为lambda 格式的 map集合
        Map<SFunction<User, ?>, Object> map = new HashMap<>();

        //查询结果
        List<User> list;

        /***
         * allEq
         * 例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null
         * 例2: allEq({id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'
         */

        //添加条件构造 相当于sql语句中 where user_id = #{user1.getUserId()} and username = #{user1.getUsername()} and password = #{user1.getPassword()}
        map.put(User::getUserId,user1.getUserId());
        map.put(User::getUsername,user1.getUsername());
        map.put(User::getPassword,user1.getPassword());

        //满足 map 中所有条件的构造器
        wrapper = new QueryWrapper<>();
        wrapper.lambda().allEq(map);
        //查询满足所有符合构造器条件的信息
        list = mybatisPlusTestService.list(wrapper);
        log.info("user1.getUserId():"+user1.getUserId());
        log.info("user1.getUsername():"+user1.getUsername());
        log.info("user1.getPassword():"+user1.getPassword());
        list.forEach(s -> log.info(s.toString()));

        /***
         * eq   等于 =
         * 例: eq("name", "老王")--->name = '老王'
         */
        wrapper = new QueryWrapper<>();
        //添加条件构造 相当于sql语句中  where password = #{user1.getPassword()}
        wrapper.lambda().eq(User::getPassword,user1.getPassword());
        //查询满足所有符合构造器条件的信息
        list = mybatisPlusTestService.list(wrapper);
        log.info("user1.getPassword():"+user1.getPassword());
        list.forEach(s -> log.info(s.toString()));

        /***
         * ne   不等于 <>
         */
        wrapper = new QueryWrapper<>();

        //添加条件构造 相当于sql语句中  where password = #{user1.getPassword()} and username != #{user1.getUsername()}
        //构造一个条件后，可在后面继续追加条件，相当于 sql多个条件中间添加 and 连接符
        //也可另起一行
        wrapper.lambda().eq(User::getPassword,user1.getPassword()).ne(User::getUsername,user1.getUsername());

        list = mybatisPlusTestService.list(wrapper);
        log.info("user1.getPassword():"+user1.getPassword());
        log.info("user1.getUsername():"+user1.getUsername());
        list.forEach(s -> log.info(s.toString()));

        /***
         * gt   大于 >
         * 例: gt("age", 18)--->age > 18
         */
        wrapper = new QueryWrapper<>();
        // 时间大于昨天的 条件
        wrapper.lambda().gt(User::getCreateTime, DateUtil.yesterday());
        // 密码为本机mac的 条件
        wrapper.lambda().eq(User::getPassword,getMac());
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * ge   大于等于 >=
         * lt   小于 <
         * le   小于等于 <=
         * 值比较的参照上面实例
         */

        /***
         *between   BETWEEN 值1 AND 值2
         */
        wrapper = new QueryWrapper<>();
        // 创建时间在 今天开始 到 今天结束的 所有信息
        wrapper.lambda().between(User::getCreateTime,DateUtil.beginOfDay(new Date()),DateUtil.endOfDay(new Date()));
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * notBetween   NOT BETWEEN 值1 AND 值2
         * 参照 between
         */

        /***
         * like     LIKE '%值%'
         * 例: like("name", "王")--->name like '%王%'
         */
        wrapper = new QueryWrapper<>();
        // 名称中带有 ‘wrapper’ 的信息
        wrapper.lambda().like(User::getUsername,"wrapper");
        wrapper.lambda().between(User::getCreateTime,DateUtil.beginOfDay(new Date()),DateUtil.endOfDay(new Date()));
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * notLike  NOT LIKE '%值%'
         * likeLeft LIKE '%值'
         * likeRight    LIKE '值%'
         * 参照 like 实例
         */

        /***
         * isNull   字段 IS NULL
         * 例: isNull("name")--->name is null
         */
        wrapper = new QueryWrapper<>();
        // 数据库中 mobile 字段为 null的字段
        wrapper.lambda().isNull(User::getMobile);
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * isNotNull    字段 IS NOT NULL
         * 例: isNotNull("name")--->name is not null
         * 参照 isNull 实例
         */

        /***
         * in   字段 IN (value.get(0), value.get(1), ...)
         */
        wrapper = new QueryWrapper<>();
        //username 字段 为 #{CONTENT + "wrapper2"} 或者 #{CONTENT + "wrapper3"} 的信息
        wrapper.lambda().in(User::getUsername,CONTENT + "wrapper2",CONTENT + "wrapper3");
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * notIn    字段 NOT IN (value.get(0), value.get(1), ...)
         * 例: notIn("age",{1,2,3})--->age not in (1,2,3)
         * 参照 in 实例
         */

        /***
         * inSql    字段 IN ( sql语句 )
         * 例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
         * 例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)
         */
        wrapper = new QueryWrapper<>();
        //先查询 sql 语句的结果， 再查询 字段username 中包含结果集的 信息
        String sql = "select username from tb_user where password = '"+getMac()+"' group by username";
        wrapper.lambda().inSql(User::getUsername,sql);
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         *notInSql  字段 NOT IN ( sql语句 )
         * 例: notInSql("age", "1,2,3,4,5,6")--->age not in (1,2,3,4,5,6)
         * 例: notInSql("id", "select id from table where id < 3")--->id not in (select id from table where id < 3)
         * 参照 inSql 的实例
         */

        /***
         * groupBy
         * 分组：GROUP BY 字段, ...
         * 例: groupBy("id", "name")--->group by id,name
         */
        wrapper = new QueryWrapper<>();
        //根据 password 分组
        wrapper.lambda().groupBy(User::getPassword);
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * orderByDesc  排序：ORDER BY 字段, ... DESC
         * 例: orderByDesc("id", "name")--->order by id DESC,name DESC
         */
        wrapper = new QueryWrapper<>();
        //根据 创建时间 降序排序
        wrapper.lambda().orderByDesc(User::getCreateTime);
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * orderByAsc   排序：ORDER BY 字段, ... ASC
         * orderBy      排序：ORDER BY 字段, ...
         * 参照 orderByDesc 实例
         */

        /***
         * having   HAVING ( sql语句 )
         * 例: having("sum(age) > 10")--->having sum(age) > 10
         * 例: having("sum(age) > {0}", 11)--->having sum(age) > 11
         * 实例待补充
         */

        /***
         * func     func 方法(主要方便在出现if...else下调用不同方法能不断链)
         * 例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
         * 实例待补充
         */

        /***
         * or   拼接 OR
         * 主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
         */
        wrapper = new QueryWrapper<>();
        //字段 username 的值为 #{user1.getUsername()} 或者 #{user2.getUsername()}
        wrapper.lambda().eq(User::getUsername,user1.getUsername()).or().eq(User::getUsername,user2.getUsername());
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * and  AND 嵌套
         * 例: and(i -> i.eq("name", "李白").ne("status", "活着"))--->and (name = '李白' and status <> '活着')
         */
        wrapper = new QueryWrapper<>();
        //and (username = #{user1.getUsername()} and password = #{getMac()})
        wrapper.lambda().and(i -> i.eq(User::getUsername,user1.getUsername()).eq(User::getPassword,getMac()));
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * nested   正常嵌套 不带 AND 或者 OR
         * 例: nested(i -> i.eq("name", "李白").ne("status", "活着"))--->(name = '李白' and status <> '活着')
         * 参考 and 实例
         */

        /***
         * apply    拼接 sql
         * 例: apply("id = 1")--->id = 1
         * 例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
         * 例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
         * 注意事项:
         * 该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!
         */
        wrapper = new QueryWrapper<>();
        wrapper.lambda().apply("date_format(create_time,'%Y-%m-%d') = {0}","2020-07-06");
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));

        /***
         * notExists    拼接 NOT EXISTS ( sql语句 )
         * exists       拼接 EXISTS ( sql语句 )
         * 实例待补充
         */

        mybatisPlusTestService.remove(tempWrapper);
        return "OK";
    }

    /**
     * 条件构造器的 QueryWrapper 类
     */
    @GetMapping("/query-wrapper")
    public String queryWrapper() {

        //查询条件构造器
        QueryWrapper<User> wrapper ;
        List list ;

        /***
         * select   设置查询字段
         * 例: select("id", "name", "age")
         * 例: select(i -> i.getProperty().startsWith("test"))
         */
        wrapper = new QueryWrapper<>();

        //清除多余数据，不用关心
        UpdateWrapper<User> tempWrapper = new UpdateWrapper<>();
        tempWrapper.lambda().eq(User::getPassword,getMac());
        mybatisPlusTestService.remove(tempWrapper);

        // 先插入（批量）
        Collection<User> entityList = new ArrayList<>();
        User user1 = InstanceUtil.getUser(CONTENT + "wrapper1");
        User user2 = InstanceUtil.getUser(CONTENT + "wrapper2");
        User user3 = InstanceUtil.getUser(CONTENT + "wrapper3");
        user3.setMobile(null);
        User user4 = InstanceUtil.getUser(CONTENT + "wrapper4");
        User user5 = InstanceUtil.getUser(CONTENT + "wrapper5");
        entityList.add(user1);
        entityList.add(user2);
        entityList.add(user3);
        entityList.add(user4);
        entityList.add(user5);
        mybatisPlusTestService.saveBatch(entityList);
        entityList.forEach(s -> log.info("\r\n批量新增：\r\n" + s));

        //只查询 以下三个字段的值
        wrapper.lambda().select(User::getUsername,User::getPassword,User::getCreateTime).eq(User::getPassword,getMac());
        list = mybatisPlusTestService.listMaps(wrapper);
        list.forEach(s -> log.info(s.toString()));

        mybatisPlusTestService.remove(tempWrapper);
        return "OK";
    }

    /**
     * 条件构造器的 QueryWrapper 类
     */
    @GetMapping("/update-wrapper")
    public String updateWrapper() {

        //清除多余数据，不用关心
        UpdateWrapper<User> tempWrapper = new UpdateWrapper<>();
        tempWrapper.lambda().eq(User::getPassword,getMac());
        mybatisPlusTestService.remove(tempWrapper);

        // 先插入（批量）
        Collection<User> entityList = new ArrayList<>();
        User user1 = InstanceUtil.getUser(CONTENT + "wrapper1");
        User user2 = InstanceUtil.getUser(CONTENT + "wrapper2");
        User user3 = InstanceUtil.getUser(CONTENT + "wrapper3");
        User user4 = InstanceUtil.getUser(CONTENT + "wrapper4");
        User user5 = InstanceUtil.getUser(CONTENT + "wrapper5");
        entityList.add(user1);
        entityList.add(user2);
        entityList.add(user3);
        entityList.add(user4);
        entityList.add(user5);
        mybatisPlusTestService.saveBatch(entityList);
        entityList.forEach(s -> log.info("\r\n批量新增：\r\n" + s));


        UpdateWrapper<User> wrapper ;
        List list ;


        /***
         * set  SQL SET 字段
         * 例: set("name", "老李头")
         * 例: set("name", "")--->数据库字段值变为空字符串
         * 例: set("name", null)--->数据库字段值变为null
         */
        wrapper = new UpdateWrapper();
        //将所有 username字段的值 设置为 ‘wrapper’
        wrapper.lambda().set(User::getUsername,"wrapper");
        //设置约束条件，不然为全表修改
        wrapper.lambda().eq(User::getPassword,getMac());
        //修改
        mybatisPlusTestService.update(wrapper);
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));



        /***
         * setSql   设置 SET 部分 SQL
         * 例: setSql("name = '老李头'")
         */
        wrapper = new UpdateWrapper();
        wrapper.lambda().setSql("username = 'wrapper1'");
        wrapper.lambda().eq(User::getPassword,getMac());
        //修改
        mybatisPlusTestService.update(wrapper);
        list = mybatisPlusTestService.list(wrapper);
        list.forEach(s -> log.info(s.toString()));


        mybatisPlusTestService.remove(tempWrapper);
        return "OK";
    }


}
